package com.sapient;

import java.io.IOException;
import java.sql.*;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 * Servlet implementation class LocationCreate
 */
@WebServlet("/LocationCreate")
public class LocationCreate extends HttpServlet {
	private static final long serialVersionUID = 1L;
	String[][] details = null;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LocationCreate() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int rows = 0;
		ResultSet rs = null;
		Connection conn = null;
		HttpSession session=request.getSession();
		String user = (String) session.getAttribute("role");
		try
		{ 
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
			Statement st = conn.createStatement(); 
			String SQL = "";

			if(user.equals("user"))
				SQL =  "select LOCATIONNAME, description, flag from Location where flag='N'";
			else
				SQL = "select LOCATIONNAME, description, flag from Location";
			rs = st.executeQuery(SQL);
			while(rs.next()){
				rows++;
			}
			details = new String[rows][4];
			rs= st.executeQuery(SQL);

			int count = 0;
			while(rs.next())
			{
				details[count][0] = rs.getString(1);
				details[count][1] = rs.getString(2);
				details[count][2] = rs.getString(3);
				count++; 
			}

			conn.close(); 
		}catch (Exception e) { 
			System.err.println("Got an exception! "); 
			System.err.println(e.getMessage()); 
			e.printStackTrace();
		} 
		request.setAttribute("locdetails", details);
		RequestDispatcher r = getServletContext().getRequestDispatcher("/Location.jsp");
		r.forward(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String location = request.getParameter("Location");
		String description = request.getParameter("Description");
		String status = "";
		HttpSession session=request.getSession();
		String user = (String) session.getAttribute("User");
		
		Connection conn = null;
		try
		{ 
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
			Statement st = conn.createStatement(); 
			String SQL1 = "select * from Location where locationname = '"+location+"'"; 
			ResultSet rs = st.executeQuery(SQL1);
			if(rs.next()){
				status = "Entity creation failed. Location already existed.";
			}
			else{
				String SQL = "INSERT INTO Location (locationid,locationname,description,lastupdateduser,lastupdateddate,flag) VALUES "
						+ "(locationseq.nextval,'"+location+"','"+ description+"','"+user+"',CURRENT_DATE, 'N')";
				System.out.println(SQL);
				st.executeUpdate(SQL);
				status = "Entity created!";
			}
			conn.close(); 
			
			request.setAttribute("status", status);
		} catch (Exception e) { 
			System.err.println("Got an exception! "); 
			System.err.println(e.getMessage()); 
			e.printStackTrace();
		} 
		doGet(request,response);
	}
}
